package com.chidopi.market.bookstore.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;

import com.chidopi.cdf.dao.AbstractBaseDaoImpl;
import com.chidopi.market.bookstore.domain.ProductID2Book;

public class ProductID2BookDaoImpl extends AbstractBaseDaoImpl<ProductID2Book>  {
	
	private static Logger logger = LoggerFactory.getLogger(ProductID2BookDaoImpl.class);

	@Override
	public int insert(ProductID2Book t) {
		String sql = "insert into productid2book (PRODUCT_ID, PRODUCT_TYPE, BOOK_ID) "
				+ "values (:product_id, :product_type, :book_id)";
		return super.execSQL(sql, t);
	}

	@Override
	public int update(ProductID2Book t) {
		String sql = "update productid2book set PRODUCT_TYPE = :product_type, BOOK_ID = :book_id where PRODUCT_ID = :product_id";
		return super.execSQL(sql, t);
	}

	@Override
	public int delete(ProductID2Book t) {
		String sql = "delete from productid2book where PRODUCT_ID = :product_id";
		return super.execSQL(sql, t);
	}

	@Override
	public List<ProductID2Book> find(String sqlWhere, Object... args) {
		if (sqlWhere == null || sqlWhere.trim().equals("")) {
			String sql = "select a.*, b.BOOKNAME BOOKNAME , e.NAME SUBSCRIBENAME " +
					     "from productid2book a " +
					     "left join bookitems b on a.BOOK_ID = b.ID and a.PRODUCT_TYPE = 1 " +
					     "left join (select c.ID, d.NAME from  subscribe4ios c " +
					                 " left join subscriptions d on c.SUBSCRIBE_ID = d.ID) e " +
					     "on a.BOOK_ID = e.id and a.PRODUCT_TYPE = 2 order by a.PRODUCT_ID ";
			return super.simpleJdbcTemplate.query(sql, this.getRowMapper());
		} else {
			logger.debug("sqlWhere:[" + sqlWhere + "]/params:" + Arrays.asList(args).toString());						
			String sql = "select a.*, b.BOOKNAME BOOKNAME , e.NAME SUBSCRIBENAME " +
					     "from productid2book a " +
					     "left join bookitems b on a.BOOK_ID = b.ID and a.PRODUCT_TYPE = 1 " +
					     "left join (select c.ID, d.NAME from  subscribe4ios c " +
					                 " left join subscriptions d on c.SUBSCRIBE_ID = d.ID) e " +
					     "on a.BOOK_ID = e.id and a.PRODUCT_TYPE = 2 where " + sqlWhere + "  order by a.PRODUCT_ID ";
			logger.debug("sql: " + sql);
			List<ProductID2Book> list = super.simpleJdbcTemplate.query(sql, this.getRowMapper(), args);
			if (list == null) {
				logger.debug("query null!!!");
			} else {
				for (ProductID2Book product : list) {
					logger.debug("id=" + product.getProduct_id() + "/bookname=" + product.getBookname());
				}
			}
			return list;
		}
	}

	@Override
	public ProductID2Book findById(Object id) {
		String str_id = (String) id;
		String sql = null;
		if (str_id.matches("\\d+")) {
			sql = "a.BOOK_ID = ?";
		} else {
			sql = "a.PRODUCT_ID = ?";
		}
		//String sql = "a.PRODUCT_ID = ?";
		List<ProductID2Book> list = this.find(sql, id);
		if (0 == list.size()) return null;
		return list.get(0);
	}

	@Override
	protected String getTable() {
		return "productid2book";
	}

	@Override
	protected String getSequenceName() {
		return null;
	}

	@Override
	protected RowMapper<ProductID2Book> getRowMapper() {
		return new ParameterizedRowMapper<ProductID2Book>(){

			@Override
			public ProductID2Book mapRow(ResultSet rs, int rowNum)	throws SQLException {
				ProductID2Book vo = new ProductID2Book();
				vo.setProduct_id(rs.getString("PRODUCT_ID"));
				int type = rs.getInt("PRODUCT_TYPE");
				vo.setProduct_type(type);
				vo.setBook_id(rs.getInt("BOOK_ID"));
				if(1 == type){
					vo.setBookname(rs.getString("BOOKNAME"));
				}else{
					vo.setBookname(rs.getString("SUBSCRIBENAME"));
				}
				return vo;
			}
		};	
    }

}
